﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;

namespace DataInsight
{
    class Program
    {
        static void Main(string[] args)
        {
            //GenerateBarChart();
            //return;
            ConstructHierarchicalStatistic(args[0], args[1]);
        }

        public static Dictionary<String,Int32> ConstructHierarchicalStatistic(String rootTopic, String fileName)
        {
            if (File.Exists(fileName)) File.Delete(fileName);

            FileStream fs = File.Open(fileName, FileMode.OpenOrCreate);
            StreamWriter sw = new StreamWriter(fs);

            string connString = @"Data Source=PQOCSTSDB01.redmond.corp.microsoft.com;Initial Catalog=AzureSR;Integrated Security=True";
            SqlConnection conn = new SqlConnection(connString);
            String cmdTextSupportTopic = @"select distinct st.SupportTopicFullPath from dbo.vwDimSupportTopic st with (nolock) where st.SupportTopicFullPath like '{0}%'";
            String cmdTextSR = @"select COUNT( fsr.ServiceRequestNumber) from dbo.vwFactServiceRequest fsr
inner join dbo.vwDimSupportTopic st with(nolock) on fsr.CurrentSupportTopicDerivedID=st.SupportTopicDerivedID
left join dbo.vwDimServiceRequestAttribute att with (nolock) on fsr.SRAttributesDerivedID=att.ServiceRequestAttributesDerivedID
where st.SupportTopicFullPath like '{0}%' and fsr.SupportLanguageDerivedID in (6,7)
and fsr.SupportLanguageDerivedID in (6,7)  --en-GB  en-us
and st.IsActive=1
and att.SRType='Incident'
and att.SRStatus='Closed'";
            String cmdTextSREdge = @"select COUNT( fsr.ServiceRequestNumber) from dbo.vwFactServiceRequest fsr
inner join dbo.vwDimSupportTopic st with(nolock) on fsr.CurrentSupportTopicDerivedID=st.SupportTopicDerivedID
left join dbo.vwDimServiceRequestAttribute att with (nolock) on fsr.SRAttributesDerivedID=att.ServiceRequestAttributesDerivedID
where st.SupportTopicFullPath = '{0}' and fsr.SupportLanguageDerivedID in (6,7)
and fsr.SupportLanguageDerivedID in (6,7)  --en-GB  en-us
and st.IsActive=1
and att.SRType='Incident'
and att.SRStatus='Closed'";


            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = String.Format(cmdTextSupportTopic, rootTopic);
            conn.Open();
            SqlDataReader rdr = cmd.ExecuteReader();
            List<String> rawTopics = new List<string>();

            Dictionary<String, Int32> statistic = new Dictionary<string, int>();
            Dictionary<String, Int32> edgeStatistic = new Dictionary<string, int>();

            while (rdr.Read())
            {
                //load all the support fullpaths
                rawTopics.Add(rdr["SupportTopicFullPath"].ToString());
            }
            rdr.Close();
            //Int32 brk = 20;

            List<List<String>> rawTopicsNormalized = new List<List<string>>();
            Int32 progress = 0;
            foreach (var rawTopic in rawTopics)
            {
                rawTopicsNormalized.Add(new List<String>(rawTopic.Split('\\')));
                cmd.CommandText = String.Format(cmdTextSR, rawTopic);
                statistic.Add(rawTopic, (Int32)cmd.ExecuteScalar());
                cmd.CommandText = String.Format(cmdTextSREdge, rawTopic);
                edgeStatistic.Add(rawTopic, (Int32)cmd.ExecuteScalar());
                progress++;
                Console.Write("\r{0,4}/{1,4} processed", progress, rawTopics.Count);
                //brk--;
                //if (brk == 0) break;
            }

            Console.WriteLine();
            Console.SetOut(sw);

            #region old
            //Int32 i = 0;
            //Int32 maxDepth = rawTopicsNormalized.Max(s => s.Count);
            //StringBuilder sb = new StringBuilder();
            //while (i < maxDepth)
            //{
            //    foreach(List<String> rawTopic in rawTopicsNormalized)
            //    {
            //        if(i<rawTopic.Count)
            //            try
            //            {                            
            //                for (Int32 j = 0; j <= i; j++)
            //                {
            //                    sb.Append(rawTopic[j]);
            //                    if (j != i) sb.Append("\\");
            //                }
            //                statistic.Add(sb.ToString(), 0);
            //            }
            //            catch {/*key duplication is expected here.*/ }
            //            finally { sb.Clear(); }
            //    }
            //    i++;
            //}
            #endregion

            var o1 = statistic.OrderBy(item => item.Key).ToList();
            //foreach(var item in o1)
            //{
            //    Console.WriteLine(item.Key);
            //}

            foreach(var item1 in o1)
            {
                Console.WriteLine("[{0,8}]:{1}", item1.Value, item1.Key);
                Int32 level1 = item1.Key.Count(ch => ch == '\\');
                Console.WriteLine("\t[{0,8}]:{1}", edgeStatistic[item1.Key], item1.Key);

                foreach (var item2 in o1)
                {
                    Int32 level2 = item2.Key.Count(ch => ch == '\\');
                    if (level2 - level1 == 1 && item2.Key.Contains(item1.Key))
                        Console.WriteLine("\t[{0,8}]:{1}", item2.Value, item2.Key);
                }
            }

            sw.Close();
            fs.Close();
            return statistic;

        }

        public static void GenerateBarChart()
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            //add data 
            xlWorkSheet.Cells[1, 1] = "";
            xlWorkSheet.Cells[1, 2] = "Student1";
            xlWorkSheet.Cells[1, 3] = "Student2";
            xlWorkSheet.Cells[1, 4] = "Student3";

            xlWorkSheet.Cells[2, 1] = "Term1";
            xlWorkSheet.Cells[2, 2] = "80";
            xlWorkSheet.Cells[2, 3] = "65";
            xlWorkSheet.Cells[2, 4] = "45";

            xlWorkSheet.Cells[3, 1] = "Term2";
            xlWorkSheet.Cells[3, 2] = "78";
            xlWorkSheet.Cells[3, 3] = "72";
            xlWorkSheet.Cells[3, 4] = "60";

            xlWorkSheet.Cells[4, 1] = "Term3";
            xlWorkSheet.Cells[4, 2] = "82";
            xlWorkSheet.Cells[4, 3] = "80";
            xlWorkSheet.Cells[4, 4] = "65";

            xlWorkSheet.Cells[5, 1] = "Term4";
            xlWorkSheet.Cells[5, 2] = "75";
            xlWorkSheet.Cells[5, 3] = "82";
            xlWorkSheet.Cells[5, 4] = "68";

            Excel.Range chartRange;

            Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
            Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
            Excel.Chart chartPage = myChart.Chart;

            chartRange = xlWorkSheet.get_Range("A1", "d5");
            chartPage.SetSourceData(chartRange, misValue);
            chartPage.ChartType = Excel.XlChartType.xlColumnClustered;

            xlWorkBook.SaveAs("d:\\csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

        }

        private static void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                Console.WriteLine("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }
        
    }
}
